﻿using System.Data;
using System.Data.SqlClient;

namespace Connect_to_SQL_Server_using_ADO.NET
{
    public class SqlClientContactsRepository : IContactsRepository
    {
        SqlDataAdapter da;
        SqlConnection cs;

        public SqlClientContactsRepository( SqlConnection cs)
        {
            this.da = new SqlDataAdapter();
            this.cs = cs;
        }
        public void Update(int id, string firstName, string lastName)
        {
            da.UpdateCommand = new SqlCommand("UPDATE tblContacts SET FIRSTNAME = @FIRSTNAME, LASTNAME = @LASTNAME WHERE ID = @ID", cs);
            da.UpdateCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = firstName;
            da.UpdateCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value = lastName;
            da.UpdateCommand.Parameters.Add("@ID", SqlDbType.Int).Value = id;
            cs.Open();
            da.UpdateCommand.ExecuteNonQuery();
            cs.Close();
        }

        public void Add(string firstname, string lastname)
        {
            da.InsertCommand = new SqlCommand("IF NOT EXISTS (SELECT * FROM tblContacts WHERE FIRSTNAME = @FIRSTNAME AND LASTNAME = @LASTNAME)INSERT INTO tblContacts VALUES (@FIRSTNAME,@LASTNAME)", cs);
            da.InsertCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = firstname;
            da.InsertCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value = lastname;
            cs.Open();
            da.InsertCommand.ExecuteNonQuery();
            cs.Close();
            
        }

        public void Delete(string firstname, string lastname)
        {
            da.DeleteCommand = new SqlCommand("DELETE tblContacts WHERE FIRSTNAME = @FIRSTNAME AND LASTNAME = @LASTNAME", cs);
            da.DeleteCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = firstname;
            da.DeleteCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value = lastname;
            cs.Open();
            da.DeleteCommand.ExecuteNonQuery();
            cs.Close();
        }
        public void Read(DataSet ds)
        {
            da.SelectCommand = new SqlCommand("SELECT * FROM tblContacts", cs);
            ds.Clear();
            da.Fill(ds);

        }
    }
}
